﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NangLuongTieuDiemDTO.DTO;
using System.Data.SqlClient;

namespace NangLuongTieuDiemDAO.DAO
{
    public class SoGioNangDAO
    {
        #region "[Insert]"
        public static int Insert(SoGioNangDTO _obj)
        {
            int IDResult = -1;
            try {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "insert into SoGioNang(TenKhuVuc,SoGioNang,Xoa,GhiChu) values(@TenKhuVuc,@SoGioNang,@Xoa,@GhiChu)";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.AddWithValue("@TenKhuVuc", _obj.TenKhuVuc);
                cmd.Parameters.AddWithValue("SoGioNang", _obj.SoGioNang);
                cmd.Parameters.AddWithValue("Xoa", _obj.Xoa);
                cmd.Parameters.AddWithValue("GhiChu", _obj.GhiChu);
                cmd.ExecuteNonQuery();

                // lay ID cua dong insert

                strSQL = "Select @@IDENTITY";
                cmd = new SqlCommand(strSQL, cn);
                IDResult = Convert.ToInt32(cmd.ExecuteScalar());

                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Insert", ex);
            }
            return IDResult;
        }
        #endregion

        #region "[Delete]"
        public static bool Delete(int _ID)
        {
            bool isSuccess = false;
                
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "Delete SoGioNang where ID = " + _ID.ToString();

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                int ID = cmd.ExecuteNonQuery();
                if (ID > 0)
                    isSuccess = true;
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Delete", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Update]"
        public static bool Update(SoGioNangDTO _obj)
        {
            bool isSuccess = false;
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "Update SoGioNang Set TenKhuVuc = @TenKhuVuc, SoGioNang=@SoGioNang,Xoa=@Xoa,GhiChu=@GhiChu where ID = @ID";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                cmd.Parameters.AddWithValue("@TenKhuVuc", _obj.TenKhuVuc);
                cmd.Parameters.AddWithValue("@SoGioNang", _obj.SoGioNang);
                cmd.Parameters.AddWithValue("@Xoa", _obj.Xoa);
                cmd.Parameters.AddWithValue("@GhiChu", _obj.GhiChu);
                int ID = cmd.ExecuteNonQuery();
                if (ID > 0)
                {
                    isSuccess = true;
                }
               
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Update", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Getall]"
        public static List<SoGioNangDTO> GetAll()
        {
            List<SoGioNangDTO> listResult = new List<SoGioNangDTO>();
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "select * from SoGioNang";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SoGioNangDTO Item = new SoGioNangDTO();
                    Item.ID = int.Parse(reader["ID"].ToString());
                    Item.TenKhuVuc = reader["TenKhuVuc"].ToString();
                    Item.SoGioNang = double.Parse(reader["SoGioNang"].ToString());
                    Item.Xoa = bool.Parse(reader["Xoa"].ToString());
                    Item.GhiChu = reader["GhiChu"].ToString();

                    listResult.Add(Item);
                }
                reader.Close();
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("GetAll", ex);
            }
            return listResult;
        }
        #endregion

        #region "[Select By primary key]"
        public static SoGioNangDTO SelectByPrimaryKey(int _ID)
        {
            SoGioNangDTO objResult = null;
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "Select * from SoGioNang where ID = " + _ID.ToString();

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    objResult = new SoGioNangDTO();
                    objResult.ID = int.Parse(reader["ID"].ToString());
                    objResult.TenKhuVuc = reader["TenKhuVuc"].ToString();
                    objResult.SoGioNang = double.Parse(reader["SoGioNang"].ToString());
                    objResult.Xoa = bool.Parse(reader["Xoa"].ToString());
                    objResult.GhiChu = reader["GhiChu"].ToString();
                }
                reader.Close();
                cn.Close();
            }
            catch(Exception ex)
            {
                InSert_LoiHeThong("SelectByPrimaryKey", ex);
            }
            return objResult;
        }
        #endregion

        #region "[Hàm insert lỗi hệ thống]"
        public static void InSert_LoiHeThong(string TenHam, Exception ex)
        {
            LoiHeThongDTO lhtDto = new LoiHeThongDTO();
            lhtDto.TenLoi = ex.Message.ToString();
            lhtDto.NgayTao = DateTime.Now;
            lhtDto.TenHamBiLoi = "SoGioNangDAO_" + TenHam;
            lhtDto.SuaLoi = false;
            lhtDto.GhiChu = "";
            LoiHeThongDAO.Insert(lhtDto);

        }
        #endregion
    }
}
